package be.DAO;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.table.DefaultTableModel;
import be.connectable.Crudable;
import be.entities.Brouwer;
public class BrouwerDAO implements Crudable<Brouwer> {
private PreparedStatement stmt;
private Connection con;
private ResultSet rs;
public DefaultTableModel internalQueryList( String query) throws ClassNotFoundException, SQLException{
ArrayList<String> al = new ArrayList<String>();
createConnection();
ResultSet rs = db_query("SELECT * FROM brouwers");
ResultSetMetaData rsmd = rs.getMetaData();
int colNo = rsmd.getColumnCount();
String[] tableColumnsName = {rsmd.getColumnName(1), rsmd.getColumnName(2), rsmd.getColumnName(3)};
DefaultTableModel aModel = new DefaultTableModel();
aModel.setColumnIdentifiers(tableColumnsName);
while(rs.next()){
Object[] objects = new Object[colNo];
for(int i=0;i<colNo;i++){
int myi = i;
myi++;
objects[i]=rs.getObject(myi);
}
aModel.addRow(objects);
}
closeConnection();
return aModel;
}
@Override
public void create(Brouwer t) throws ClassNotFoundException, SQLException {
createConnection();
String query = "INSERT INTO brouwers VALUES (null,?,?,?,?)";
this.stmt = this.con.prepareStatement(query);
this.stmt.setString(1, t.getBrNaam());
this.stmt.setString(2, t.getAdres());
this.stmt.setString(3, t.getGemeente());
this.stmt.setString(4, t.getPostCode());
this.stmt.setDouble(5, t.getOmzet());
this.stmt.executeUpdate();
closeConnection();
}
@Override
public Brouwer read(long id) throws SQLException, ClassNotFoundException {
createConnection();
String query = "SELECT * FROM brouwers WHERE brouwerNr = ?";
this.stmt = con.prepareStatement(query);
this.stmt.setLong(1, id);
this.rs = this.stmt.executeQuery();
Brouwer b = createBrouwer(this.rs);
closeConnection();
return b;
}
@Override
public List<Brouwer> readAll() throws SQLException, ClassNotFoundException {
createConnection();
this.rs = db_query("SELECT * FROM brouwers");
List<Brouwer> brouwerLijst = new ArrayList<Brouwer>();
while (rs.next()){
brouwerLijst.add(createBrouwer(rs));
}
return brouwerLijst;
}
@Override
public void update(Brouwer t) throws Exception {
// TODO Auto-generated method stub
}
@Override
public void delete(Brouwer t) throws Exception {
// TODO Auto-generated method stub
}
void createConnection() throws SQLException, ClassNotFoundException{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection ("jdbc:mysql://localhost:3306/bierenweb","root","null");
}
ResultSet db_query (String query) throws SQLException{
return stmt.executeQuery(query);
}
void closeConnection() throws SQLException{
stmt.close();
rs.close();
stmt.close();
}
Brouwer createBrouwer(ResultSet rs) throws SQLException{
Brouwer b = new Brouwer();
b.setPostCode(rs.getString("PostCode"));
b.setAdres(rs.getString("Adres"));
b.setBrNaam(rs.getString("BrNaam"));
b.setGemeente(rs.getString("gemeente"));
b.setOmzet(rs.getDouble("Omzet"));
return b;
}
}